package com.atguigu.gmallpublisher.mapper;

import com.atguigu.gmallpublisher.bean.ProductStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * 商品统计
 */
public interface ProductStatsMapper {
    // toYYYYMMDD(stt)是把日志转成数组 比如:2021-03-01 -> 20210301
    @Select(
        "select " +
            "   sum(order_amount) order_amount " +
            "from product_stats_2021 " +
            "where toYYYYMMDD(stt)=#{date}")
    BigDecimal getGMV(int date);
    
    //统计某天不同品牌商品交易额排名
    @Select(
        "select " +
            "   tm_id," +
            "   tm_name," +
            "   sum(order_amount) order_amount " +
            "from product_stats_2021 " +
            "where toYYYYMMDD(stt)=#{date} " +
            "group by tm_id,tm_name " +
            "having order_amount>0  " +
            "order by  order_amount  desc " +
            "limit #{limit} ")
    public List<ProductStats> getProductStatsByTrademark(@Param("date") int date,
                                                         @Param("limit") int limit);
    
    //统计某天不同类别商品交易额排名
    @Select(
        "select " +
            "   category3_id," +
            "   category3_name," +
            "   sum(order_amount) order_amount " +
            "   from product_stats_2021 " +
            "where toYYYYMMDD(stt)=#{date} " +
            "group by category3_id,category3_name " +
            "having order_amount>0  " +
            "order by  order_amount desc " +
            "limit #{limit}")
    public List<ProductStats> getProductStatsGroupByCategory3(@Param("date") int date,
                                                              @Param("limit") int limit);
    
    //统计某天不同SPU商品交易额排名
    @Select(
        "select " +
            "   spu_id," +
            "   spu_name," +
            "   sum(order_amount) order_amount," +
            "   sum(order_ct) order_ct " +
            "from product_stats_2021 " +
            "where toYYYYMMDD(stt)=#{date} " +
            "group by spu_id,spu_name " +
            "having order_amount>0 " +
            "order by order_amount desc " +
            "limit #{limit} ")
    public List<ProductStats> getProductStatsGroupBySpu(@Param("date") int date,
                                                        @Param("limit") int limit);
    
}
